Controlling DBOPEN and SQL Access
The DBOPEN and SQL servers can each manage SQL access to objects. Each handles a different, though overlapping, subset of SQL commands.
Using DBOPEN has several advantages:
- The SQL server only handles opening files via SQL.
DBOPEN also handles database files opened in other ways. - The SQL server only handles file accesses via ODBC from external systems.
DBOPEN also handles accesses from external systems via other methods, files as well as accesses from within the system, including STRSQL, interactive jobs or standard jobs that are not running as servers. - The SQL server only looks at accesses before they are parsed.
DBOPEN also examines them after they are parsed. - The SQL server needs to parse statements twice, once by OS400 and once by Firewall.
DBOPEN statements only need to be parsed once, by OS400, with fewer possibilities for issues arising. - The SQL server needs to check all SQL statements, each time that they are used.
In DBOPEN, if a file is kept open between consecutive uses of SQL, it only needs to be checked once. - The SQL server checks accesses to all files.
You can set DBOPEN to check only accesses to specific files. This can dramatically reduce the number of file checks.
On the other hand, several SQL verbs are only available through the SQL server. Some third-party products also require the SQL server,
To control which access requests are managed by DBOPEN and SQL, select 2. DBOPEN and SQL Settings from the Activation and Server Settings screen (STRFW> 1) as shown in Setting Firewall Rules by Server.
The Setting DB-OPEN and SQL screen appears:
| Setting DB-OPEN and SQL The DBOPEN and the SQL exit points can both be used to control file access. o SQL controls ODBC requests, including Create/Delete of file/library. o DBOPEN controls ALL file opens, remote and local (Interactive and Batch). DBOPEN also allows working with Pre-selected files to reduce overhead. Control by Exit-Point . . . . . 7 1=DBOPEN All files 2=DBOPEN Pre-selected files (*) 7=DBOPEN All files & SQL 8=DBOPEN Pre-selected files & SQL (*) 9=SQL If DBOPEN is active, SQL checks. 2 1=All types of operations 2=Only functions that do not open any file: CREATE, ALTER, DROP, CALL... Recommended values appear in pink. (*) Use F8 to access the system that assists in Pre-selecting files. More... F3=Exit F8=Pre-select files system |
You can set whether DBOPEN, SQL, or both check file accesses, and whether they handle access to all relevant files or only those that have been pre-selected, through these fields:
Control by Exit-Point
Choose from these options:
- 1=DBOPEN All files: Use DBOPEN for all files. If you choose this option, Firewall does not track accesses via the SQL exit point.
- 2=DBOPEN Pre-selected files: Use DBOPEN on accesses to files that you have specified via the Work with Object Auditing Plan screen (STRFW > 4 > 51) as shown in Defining Files for Firewall to Track. If you choose this option, Firewall does not track accesses via the SQL exit point, or to objects that have not been selected.
- 7=DBOPEN All files & SQL: (Recommended) Manage access requests for all files via both the DBOPEN and SQL servers.
- 8=DBOPEN Pre-selected files & SQL: (Recommended) Use both DBOPEN and SQL on accesses to files that you have specified via the Work with Object Auditing Plan screen (STRFW > 4 > 51) as shown in Defining Files for Firewall to Track. If you choose this option, Firewall does not track accesses via the SQL exit point, or to objects that have not been selected.
- 9=SQL: Use the SQL server for all accesses. If you choose this option, Firewall does not track accesses via the DBOPEN server.
If DBOPEN is active, SQL checks
If you have chosen to activate Firewall for both DBOPEN and SQL in options 7 and 8 of Control by Exit-Point:
- 1=All types of operations: Firewall checks both DBOPEN and SQL access for all operations.
- 2=Only functions that do not open any file: CREATE, ALTER, DROP, CALL...: Firewall only checks the SQL server for accesses that are not available through DBOPEN, which do not open files.
To control further settings for DBOPEN, press the PageDown key. Additional fields appear:
| DB-OPEN Additional Settings DBOPEN usage can be further adjusted. Control selectively . . . . . N Y=ODBC+STRSQL, S=Skip NativeIO, N=No Files to exclude . . . . . . . N Y=Yes (work with), N=No Files to control . . . . . . . 3 1=Named file, 2=Based on PF, 3=Both Recommended setting is 1. Select activity by type of IO. 1=Firewall 5=Log with filenames 7=Log without filenames 9=Skip Type Native Type SQL 9 Native IO 1 Interactive STRSQL 1 OPNQRYF 1 ODBC 1 Query API 1 Other SQL 1 Other Non-SQL 1 QSQPRCED API (SAP) e.g. RUNQRY / QUERY400 1 SQL CLI Log with filenames writes an entry per controlled file. Same SQL statement can appear more than once if it includes several files. Bottom F3=Exit F12=Previous |
Control selectively
If set to Y, Firewall only examines file accesses via ODBC and SRTSQL and skips examining accesses via other methods.
If set to S, Firewall only examines file accesses not coming from NTVIO (RPG/Cobol).
If set to N, Firewall examines all file accesses, regardless of the type.
Files to exclude
To keep Firewall from examining file accesses to certain files, set this field to Y. Press Enter to select the files from a list.
Files to control
If opening a named View file, whether to check the View file or the physical file on which it is based. Options are:
- 1=Named file: Check only the named View file.
- 2=Based on PF: Check only the physical file.
- 3=Both: Check both the View file and the physical file.
The screen lists several types of input and output access requests that Firewall can examine and log. NOTE: There is a relation between the parameter Control selectively and this parameter. If you set Control selectively to Y and specify 1=Firewall or 5=Log with filenames for OPNQRYF, you will not see OPNQRYF file accesses because the access is skipped immediately if Type is not ODBC or STRSQL. Enter one of the following values in the Type field for each:
- 1=Firewall: Firewall examines each access request.
- 5=Log with filenames: Log each access, writing an entry for each file. If an SQL statement accesses multiple file, a separate line appears in the log for each file accessed.
- 7=Log without filenames: Write a single record to the log for each statement, even if it accesses multiple files.
- 9=Skip: Skip examining the access request.